package lueder.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

public class DbTools {

	private static java.sql.Connection con = null;
	//private final String url = "jdbc:microsoft:sqlserver://";
	private final static String url = "jdbc:sqlserver://";
	private final static String serverName = "localhost";
	private final static String portNumber = "1433";
	private final static String databaseName = "klk_shop";
	private final static String userName = "klk";
	private final static String password = "muhkuh";
	// Informs the driver to use server a side-cursor,
	// which permits more than one active statement
	// on a connection.
	private final static String selectMethod = "cursor";

	// Constructor
	public DbTools() {
	}

	private static String getConnectionUrl() {
		return url + serverName + ":" + portNumber + ";databaseName="
				+ databaseName + ";selectMethod=" + selectMethod + ";";
	}

	public static java.sql.Connection getConnection() {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			con = java.sql.DriverManager.getConnection(getConnectionUrl(),
					userName, password);
			if (con != null)
				System.out.println("Connection Successful!");
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("Error Trace in getConnection() : "
					+ e.getMessage());
		}
		return con;
	}

	/*
	 * Display the driver properties, database details
	 */

	public static String displayDbProperties() {
		java.sql.DatabaseMetaData dm = null;
		java.sql.ResultSet rs = null;
		String htmlreturn = "";
		try {
			con = getConnection();
			if (con != null) {
				dm = con.getMetaData();
				htmlreturn = "Driver Information<p>"
				+"<p>Driver Name: " + dm.getDriverName()
				+"<p>Driver Version: " + dm.getDriverVersion()
				+"<p>Database Information "
				+"<p>Database Name: " + dm.getDatabaseProductName()
				+"<p>Database Version: " + dm.getDatabaseProductVersion()
				+ "<p>Avalilable Catalogs ";
				rs = dm.getCatalogs();
				htmlreturn += "<ul>";
				while (rs.next()) {
					htmlreturn += "<li>catalog: " + rs.getString(1);
				}
				htmlreturn += "</ul>";
				rs.close();
				rs = null;
				htmlreturn += closeConnection();
			} else
				return "Error: No active Connection";
		} catch (Exception e) {
			return htmlreturn += "<p>"+e.toString();
		}
		dm = null;
		return htmlreturn;
	}

	private static String closeConnection() {
		try {
			if (con != null)
				con.close();
			con = null;
		} catch (Exception e) {
			return e.toString();
		}
		return "";
	}

	public static String getTableFromDb(String sql) {
		Connection cn = null;
		Statement st = null;
		ResultSet rs = null;
		String table = "";
		cn = DbTools.getConnection();
		try {
			table = "<table border=\"1\"><tr>";
			st = cn.createStatement();
			rs = st.executeQuery(sql);
			// Get meta data:
			ResultSetMetaData rsmd = rs.getMetaData();
			int i, n = rsmd.getColumnCount();
			// Print table content:
			for (i = 1; i <= n; i++)
				// Attention: first column with 1 instead of 0
				table += "<th>"+rsmd.getColumnName(i)+"</th>";
			table += "</tr>";
			
			while (rs.next()) {
				table += "<tr>";
				for (i = 1; i <= n; i++)
				{
					// Attention: first column with 1 instead of 0
					table += "<td>"+rs.getString(i)+"</td>";
				}
				table += "</tr></table>";
			}
		} catch (Exception ex) {
			table = "";
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (Exception ex) {/* nothing to do */
			}
			try {
				if (st != null)
					st.close();
			} catch (Exception ex) {/* nothing to do */
			}
			try {
				if (cn != null)
					cn.close();
			} catch (Exception ex) {/* nothing to do */
			}
		}
		return table;
	}
}